<?php

class m_silabus extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get last inserted id
    function get_last_inserted_id() {
        return $this->db->insert_id();
    }

    // get tahun ajaran
    function get_tahun_ajaran() {
        $sql = "SELECT tahun FROM silabus GROUP BY tahun ORDER BY tahun ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total data makul diampu
    function get_total_data_makul_diampu($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
                FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.tahun LIKE ? AND a.semester LIKE ?  
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get data makul diampu
    function get_data_makul_diampu($params) {
        $sql = "SELECT a.set_dosenajar_kd, a.tahun, a.semester, b.dosen_kd, b.makul_kd, 
                d.makul_nm, d.makul_sks 
                FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.tahun LIKE ? AND a.semester LIKE ?  
                AND b.dosen_kd = ? 
                ORDER BY a.tahun DESC, a.semester DESC, d.makul_nm ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // cek data silabus
    function get_total_data_silabus($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM silabus 
            WHERE tahun = ? AND SEMESTER = ? 
            AND dosen_kd = ? AND makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // cek data silabus by tipe
    function get_total_data_silabus_by_tipe($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM silabus 
            WHERE tahun = ? AND SEMESTER = ? 
            AND dosen_kd = ? AND makul_kd = ? 
            AND silabus_tipe = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get detail mata kuliah
    function get_detail_makul($params) {
        $sql = "SELECT * FROM mata_kuliah WHERE makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get jurusan
    function get_data_jurusan() {
        $sql = "SELECT * FROM jurusan";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data silabus
    function get_data_silabus($params) {
        $sql = "SELECT a.*, b.jurusan_nm FROM silabus a 
                JOIN jurusan b ON a.jurusan_kd = b.jurusan_kd 
                WHERE a.tahun = ? AND a.semester = ? 
                AND a.dosen_kd = ? AND a.makul_kd = ? 
                AND a.silabus_tipe = ?
                ORDER BY a.silabus_deskripsi ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail info silabus
    function get_detail_info_silabus($params) {
        $sql = "SELECT a.*, b.nama_lengkap, b.dosen_gelar, c.makul_nm, 
                c.makul_sks, d.jurusan_alias, d.jurusan_nm 
                FROM silabus a 
                JOIN users b ON a.dosen_kd = b.user_id 
                JOIN mata_kuliah c ON a.makul_kd = c.makul_kd 
                JOIN jurusan d ON a.jurusan_kd = d.jurusan_kd 
                WHERE a.silabus_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data detail silabus
    function get_detail_data_silabus($params) {
        $sql = "SELECT * FROM silabus_detail WHERE silabus_detail_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data item silabus
    function get_data_item_silabus($params) {
        $sql = "SELECT * FROM silabus_detail 
                WHERE silabus_kd = ?  
                ORDER BY minggu ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get file silabus teori
    function get_file_silabus_teori($params) {
        // get file
        $file_path = 'resource/doc/silabus/teori/' . $params . '.pdf';
        //--
        $link = 'silabus/teori/download_files/' . $params;
        if (is_file($file_path)) {
            $url = site_url($link);
            $result = '<a href="' . $url . '" class="btn btn-mini btn-square tip"><img src="' . base_url() . 'resource/doc/images/icon/download-icon.png" alt="" height="15"/> Download</a>';
        } else {
            $result = '-';
        }
        //--
        return $result;
    }

    // get file silabus praktikum
    function get_file_silabus_praktikum($params) {
        // get file
        $file_path = 'resource/doc/silabus/praktikum/' . $params . '.pdf';
        //--
        $link = 'silabus/praktikum/download_files/' . $params;
        if (is_file($file_path)) {
            $url = site_url($link);
            $result = '<a href="' . $url . '" class="btn btn-mini btn-square tip"><img src="' . base_url() . 'resource/doc/images/icon/download-icon.png" alt="" height="15"/> Download</a>';
        } else {
            $result = '-';
        }
        //--
        return $result;
    }

    // get data materi pokok
    function get_data_materi($params) {
        $sql = "SELECT * FROM silabus_materi_detail WHERE materi_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data indikator kompetensi
    function get_data_indikator($params) {
        $sql = "SELECT * FROM silabus_indikator_detail WHERE indikator_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data sumber
    function get_data_sumber($params) {
        $sql = "SELECT * FROM silabus_sumber_detail WHERE sumber_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // insert data silabus
    function insert_data_silabus($params) {
        $sql = "INSERT INTO silabus (silabus_tipe, semester, tahun, dosen_kd, makul_kd, jurusan_kd, silabus_deskripsi, 
            mdb, mdd) 
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert data item silabus
    function insert_data_item_silabus($params) {
        $sql = "INSERT INTO silabus_detail (silabus_kd, minggu, kompetensi_dasar, evaluasi, keterangan, mdb, mdd) 
            VALUES(?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert data detail materi pokok
    function insert_materi_detail($params) {
        $sql = "INSERT INTO silabus_materi_detail (materi_kd, materi_content, mdb, mdd) 
            VALUES(?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert data detail indikator kompetensi
    function insert_indikator_detail($params) {
        $sql = "INSERT INTO silabus_indikator_detail (indikator_kd, indikator_content, mdb, mdd) 
            VALUES(?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert data detail sumber
    function insert_sumber_detail($params) {
        $sql = "INSERT INTO silabus_sumber_detail (sumber_kd, sumber_content, mdb, mdd) 
            VALUES(?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // update data item silabus
    function update_data_item_silabus($params) {
        $sql = "UPDATE silabus_detail SET minggu = ?, kompetensi_dasar = ?, evaluasi = ?, keterangan = ?, mdb = ?, mdd = NOW() 
            WHERE silabus_detail_kd = ?";
        return $this->db->query($sql, $params);
    }

    // hapus data detail materi
    function delete_materi_detail($params) {
        $sql = "DELETE FROM silabus_materi_detail WHERE materi_kd = ?";
        return $this->db->query($sql, $params);
    }

    // hapus data detail indikator kompetensi
    function delete_indikator_detail($params) {
        $sql = "DELETE FROM silabus_indikator_detail WHERE indikator_kd = ?";
        return $this->db->query($sql, $params);
    }

    // hapus data detail sumber
    function delete_sumber_detail($params) {
        $sql = "DELETE FROM silabus_sumber_detail WHERE sumber_kd = ?";
        return $this->db->query($sql, $params);
    }

    // hapus data item silabus
    function delete_data_item_silabus($params) {
        $sql = "DELETE FROM silabus_detail WHERE silabus_detail_kd = ?";
        return $this->db->query($sql, $params);
    }

    /*
     *  MONITORING
     */

    // get total dosen
    function get_total_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM
                (
                    SELECT a.* FROM users a 
                    JOIN com_role_user b ON a.user_id = b.user_id 
                    JOIN com_role c ON b.role_id = c.role_id 
                    LEFT JOIN kelas d ON a.user_id = d.dosen_kd
                    WHERE c.role_id = 4 AND d.tahun = ?  
                    AND d.semester = ? AND a.nama_lengkap LIKE ?  
                    GROUP BY a.user_id
                )r";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data dosen
    function get_data_dosen($params) {
        $sql = "SELECT a.*, d.tahun, d.semester FROM users a 
                JOIN com_role_user b ON a.user_id = b.user_id 
                JOIN com_role c ON b.role_id = c.role_id 
                LEFT JOIN kelas d ON a.user_id = d.dosen_kd
                WHERE c.role_id = 4 AND d.tahun = ?  
                AND d.semester = ? AND a.nama_lengkap LIKE ? 
                GROUP BY a.user_id 
                ORDER BY a.nama_lengkap ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total makul per dosen
    function get_total_makul_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get data makul dosen
    function get_data_makul_dosen($params) {
        $sql = "SELECT c.* FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN mata_kuliah c ON b.makul_kd = c.makul_kd 
                WHERE a.tahun = ? AND a.semester = ?   
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // link download data silabus
    function link_data_silabus($params) {
        $sql = "SELECT * FROM silabus 
            WHERE silabus_tipe = ? AND tahun = ? 
            AND semester = ? AND dosen_kd = ? 
            AND makul_kd = ? 
            ORDER BY mdd DESC 
            LIMIT 0, 1";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            $link = 'monitoring/silabus/download_files/' . $result['silabus_kd'];
            $url = site_url($link);
            $result = '<a href="' . $url . '" class="btn btn-mini btn-square tip"><img src="' . base_url() . 'resource/doc/images/icon/download-icon.png" alt="" height="15"/> Download Silabus</a>';
            return $result;
        } else {
            return '<em>Tidak terdapat data silabus</em>';
        }
    }

}